<?php

/**
 * 测试 MySQL Timestamp 字段相关
 *
 * @author LJ <jun.lu.726@gmail.com>
 * $Id: Timestamp.php 95 2012-09-22 17:31:46Z silverd30@gmail.com $
 */

class Controller_Test_Timestamp extends Controller_Test_Abstract
{
    protected $_db;
    protected $_urlLink = '/_test/timestamp/';

    public function init()
    {
        $this->_db = Com_DB::get('test');
    }

    public function createAction()
    {
        $sql = "
CREATE TABLE `timestamp_test` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `flag` INT(11) NOT NULL DEFAULT '0',
    `created` TIMESTAMP NOT NULL,
    `updated` TIMESTAMP NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;
";
        echo $this->_db->query($sql);
    }

    public function insertTimeAction()
    {
        $this->_db->query('TRUNCATE TABLE `timestamp_test`');

        $setArr = array(
            'name'        => time() . 'name',
            'created' => time(),
        );
        $insertId = $this->_db->insert('timestamp_test', $setArr);

        echo '<h2>insert[时间截]</h2>';
        var_dump($insertId);
        echo '<br />';
        exit;
    }

    public function insertDateAction()
    {
        $this->_db->query('TRUNCATE TABLE `timestamp_test`');

        $setArr = array(
            'name'        => time() . 'name',
            'created' => date("Y-m-d H:i:s", time()),
        );
        $insertId = $this->_db->insert('timestamp_test', $setArr);

        echo '<h2>insert[格式时间]</h2>';
        var_dump($insertId);
        echo '<br />';
    }

    public function readAction()
    {
        $data = $this->_db->fetchAll("select * from `timestamp_test`");
        echo '<h2>获取全部数据</h2>';
        echo "<pre>";
        var_dump($data);
        echo '<br />';
    }

    public function updateAction()
    {
        echo '<h2>更新数据</h2>';
        echo "<pre>";
        echo $this->_db->update('timestamp_test', array('flag' => time(), 'updated' => date('Y-m-d H:i:s')), array(), -1);
        echo '<br />';

        $data = $this->_db->fetchAll("select * from `timestamp_test`");
        echo '<h2>获取全部数据</h2>';
        echo "<pre>";
        var_dump($data);
        echo '<br />';
    }

    public function compareAction()
    {
        $data = $this->_db->fetchAll("select * from `timestamp_test` where updated < '" . date("Y-m-d H:i:s", time() + 1) . "'");
        echo '<h2>查询比当前时间小的[格式时间]</h2>';
        echo "<pre>";
        var_dump($data);
        echo '<br />';

        $data = $this->_db->fetchAll("select * from `timestamp_test` where updated < '" . (time() + 1) . "'");
        echo '<h2>查询比当前时间小的[time()]</h2>';
        echo "<pre>";
        var_dump($data);
        echo '<br />';
    }

    public function __destruct()
    {
        echo '<h2>暂得出结果为，timestamp插入时格式必需为数据设定的格式， 不可以为时间戳。<BR>当字段设定为timestamp 并在ON UPDATE CURRENT_TIMESTAMP时，<BR>
           timestamp 可进行比较。<BR>
           可进行排序 order by <BR>
            当这条记录更新不需要做此字段更新，自动会更新此字段</h2>';
    }
}